﻿using Microsoft.Extensions.Configuration;
using System;
using System.Data.SqlClient;
using System.Data;
using System.Collections;
using System.IO;

namespace SutdertExciser
{
    class Program
    {
        static void Main(string[] args)
        {
            ExecuteScalar();
            StoredProcedure("4");
            ExecuteCommand();
            ExecuteNonQuery();
        }
        /// <summary>
        /// 获取连接字符串
        /// </summary>
        /// <returns></returns>
        public static string GetConnectionString()
        {
            var configurationBuilder = new ConfigurationBuilder()
                .SetBasePath(Directory.GetCurrentDirectory())
                .AddJsonFile("config.json");

            IConfiguration config = configurationBuilder.Build();
            string connectionString = config["Data:DefaultConnection:ConnectionString"];
            return connectionString;
        }
        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="publisher"></param>
        private static void StoredProcedure(string stuclass)
        {
            //使用连接字符串创建连接，在使用之后关闭连接
            using (var connection = new SqlConnection(GetConnectionString()))
            {
                SqlCommand command = connection.CreateCommand();
                command.CommandText = "[dbo].[GetStudentsByClass]";
                command.CommandType = CommandType.StoredProcedure;


                SqlParameter p1 = command.CreateParameter();
                p1.SqlDbType = SqlDbType.NChar;
                p1.ParameterName = "@Class";
                p1.Value = stuclass;
                command.Parameters.Add(p1);
                connection.Open();
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        int id = (int)reader["Id"];
                        string name = (string)reader["Name"];
                        string classs = (string)reader["Class"];
                        Console.WriteLine($"{id},{name} - {classs}");
                    }
                }
            }
        }
        /// <summary>
        /// 执行ExecuteScalar查询，
        /// 返回查询所返回的结果集中第一行的第一列。 忽略其他列或行。
        /// </summary>
        private static void ExecuteScalar()
        {

            using (var connection = new SqlConnection(GetConnectionString()))
            {
                //创建sql命令字符串
                string sql = "SELECT COUNT(*) FROM [dbo].[Students]";
                SqlCommand command = connection.CreateCommand();
                command.CommandText = sql;
                connection.Open();
                object count = command.ExecuteScalar();
                Console.WriteLine($"学生个数：{count} ");
            }
        }
        /// <summary>
        /// 创建sql命令，并执行ExecuteReader，返回一个类型化的IDataReader
        /// </summary>
        public static void CreateCommand()
        {

            using (var connection = new SqlConnection(GetConnectionString()))
            {

                string sql = "SELECT [Name], [Class]FROM [dbo].[Students]";
                var command = new SqlCommand(sql, connection);
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();
            }
        }

        /// <summary>
        /// 创建带参数的命令
        /// </summary>
        public static void CreateCommandWithParameters()
        {
            using (var connection = new SqlConnection(GetConnectionString()))
            {

                string sql = "SELECT[Id], [Name], [Class]FROM [dbo].[Students] WHERE lower([Name]) LIKE @Name";
                var command = new SqlCommand(sql, connection);

                command.Parameters.AddWithValue("Name", "夏安安");
                command.Parameters.Add("NameStart", SqlDbType.NChar, 10);
                command.Parameters["Name"].Value = "江少";

                connection.Open();
                SqlDataReader reader = command.ExecuteReader();
            }
        }

        /// <summary>
        /// 参数化命令的第3种方法
        /// </summary>
        public static void ExecuteCommand()
        {
            using (var connection = new SqlConnection(GetConnectionString()))
            {
                string sql = "SELECT [Id],[Name], [Class]FROM [dbo].[Students] WHERE lower([Name]) LIKE @Name ORDER BY [Class]";
                var command = new SqlCommand(sql, connection);
                var parameter = new SqlParameter("Name", SqlDbType.NChar, 10)
                {
                    Value = "aa"
                };
                command.Parameters.Add(parameter);
                connection.Open();

                using (SqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var s = reader[0];
                        Console.WriteLine(s);
                    }
                }
            }
        }
        public static void ExecuteNonQuery()
        {
            try
            {
                using (var connection = new SqlConnection(GetConnectionString()))
                {
                    string sql = "INSERT INTO  [dbo].[Students] ([Id],[Name], [Class]) " +
                        "VALUES (@Id, @Name, @Class)";

                    var command = new SqlCommand(sql, connection);
                    command.Parameters.AddWithValue("Id", "4");
                    command.Parameters.AddWithValue("Name", "慕容云海");
                    command.Parameters.AddWithValue("Class", "神话4班");

                    connection.Open();
                    //只返回受影响的行数
                    int records = command.ExecuteNonQuery();

                    Console.WriteLine($"{records} record(s) inserted");
                }
            }
            catch (SqlException ex)
            {
                Console.WriteLine(ex.Message);
            }
        }
        /// <summary>
        /// ExecuteReader
        /// </summary>
        public static void ExecuteReader(string name)
        {
            string GetBookQuery() =>
                "SELECT [Id], [Name], [Class] FROM [dbo].[Students] WHERE lower([Name]) LIKE @Name ORDER BY [Id] DESC";

            var connection = new SqlConnection(GetConnectionString());

            var command = new SqlCommand(GetBookQuery(), connection);
            var parameter = new SqlParameter("Name", SqlDbType.NChar, 50)
            {
                Value = $"{name }%"

            };
            command.Parameters.Add(parameter);

            connection.Open();

            using (SqlDataReader student = command.ExecuteReader(CommandBehavior.CloseConnection))
            {
                while (student.Read())
                {
                    int id = student.GetInt32(0);
                    string studentname = student.ToString();
                    string studentclass = student.GetString(1);

                    Console.WriteLine($"{id}. {studentname} {studentclass} ");
                }
            }
        }
    }
}
